package com.ph.irp.base.verifyproject.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.verifyproject.dao.PBaseVerifyprojectStorageDao;
import com.ph.irp.model.PBaseVerifyprojectStorage;
import com.ph.util.DBUtil;

public class PBaseVerifyprojectStorageDaoImpl implements
		PBaseVerifyprojectStorageDao {
	private ComboPooledDataSource dataSource;
	
	public void setDataSource(ComboPooledDataSource dataSource) {
		this.dataSource = dataSource;
	}

	@Override
	public void addStorage(PBaseVerifyprojectStorage storage) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;

		StringBuilder sb = new StringBuilder();
		sb.append("insert into p_base_verifyproject_storage(StorageCode,StorageName");
		if(storage.getPBaseVerifyprojectCandiateId() != 0){
			sb.append(",CandiateID");
		}
		if(storage.getStorageAddress() != null && !storage.getStorageAddress().equals("")){
			sb.append(",StorageAddress");
		}
		if(storage.getStoragePhone() != null && !storage.getStoragePhone().equals("")){
			sb.append(",StoragePhone");
		}
		sb.append(",StorageTypeID");
		sb.append(") values('");
		sb.append(storage.getStorageCode().trim());
		sb.append("','");
		sb.append(storage.getStorageName().trim());
		sb.append("'");
		if(storage.getPBaseVerifyprojectCandiateId() != 0){
			sb.append(",");
			sb.append(storage.getPBaseVerifyprojectCandiateId());
		}
		if(storage.getStorageAddress() != null && !storage.getStorageAddress().equals("")){
			sb.append(",'");
			sb.append(storage.getStorageAddress().trim());
			sb.append("'");
		}
		if(storage.getStoragePhone() != null && !storage.getStoragePhone().equals("")){
			sb.append(",'");
			sb.append(storage.getStoragePhone().trim());
			sb.append("'");
		}
		sb.append(",");
		sb.append(storage.getPBaseVerifyprojectStoragetypeId());
		sb.append(")");
		
		try {
        //System.out.println(sb.toString());
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void deleteStorage(int storageId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("delete from p_base_verifyproject_storage where StorageID=").append(storageId);
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.execute();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public PBaseVerifyprojectStorage getStorageById(int storageId) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		PBaseVerifyprojectStorage storage = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_storage where StorageID=").append(storageId);
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			if(rs.next()){
				storage = new PBaseVerifyprojectStorage();
				storage.setStorageId(rs.getInt("StorageID"));
				storage.setStorageCode(rs.getString("StorageCode"));
				storage.setStorageName(rs.getString("StorageName"));
				storage.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				storage.setStorageAddress(rs.getString("StorageAddress"));
				storage.setStoragePhone(rs.getString("StoragePhone"));
				storage.setPBaseVerifyprojectStoragetypeId(rs.getInt("StorageTypeID"));
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return storage;
	}

	@Override
	public List<PBaseVerifyprojectStorage> getStorageList(
			PBaseVerifyprojectStorage storage) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		List<PBaseVerifyprojectStorage> list = new ArrayList<PBaseVerifyprojectStorage>();
		
		StringBuilder sb = new StringBuilder();
		sb.append("select * from p_base_verifyproject_storage where 1=1");
		if(storage.getStorageCode() != null && !storage.getStorageCode().equals("")){
			sb.append(" and StorageCode like '%");
			sb.append(storage.getStorageCode());
			sb.append("%'");
		}
		if(storage.getStorageName() != null && !storage.getStorageName().equals("")){
			sb.append(" and StorageName like '%");
			sb.append(storage.getStorageName());
			sb.append("%'");
		}
		if(storage.getPBaseVerifyprojectStoragetypeId() != 0){
			sb.append(" and StorageTypeID=");
			sb.append(storage.getPBaseVerifyprojectStoragetypeId());
		}
		if(storage.getStorageAddress() != null && !storage.getStorageAddress().equals("")){
			sb.append(" and StorageAddress like '%");
			sb.append(storage.getStorageAddress());
			sb.append("%'");
		}
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			rs = pStmt.executeQuery();
			while(rs.next()){
				PBaseVerifyprojectStorage pbvs = new PBaseVerifyprojectStorage();
				pbvs.setStorageId(rs.getInt("StorageID"));
				pbvs.setStorageCode(rs.getString("StorageCode"));
				pbvs.setStorageName(rs.getString("StorageName"));
				pbvs.setPBaseVerifyprojectCandiateId(rs.getInt("CandiateID"));
				pbvs.setStorageAddress(rs.getString("StorageAddress"));
				pbvs.setStoragePhone(rs.getString("StoragePhone"));
				pbvs.setPBaseVerifyprojectStoragetypeId(rs.getInt("StorageTypeID"));
				list.add(pbvs);
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
		return list;
	}

	@Override
	public boolean isSame(String storageCode) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		String sql = "select * from p_base_verifyproject_storage where StorageCode='"+storageCode.trim()+"'";
		
		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sql);
			rs = pStmt.executeQuery();
			if(rs.next()){
				return true;
			}else{
				return false;
			}
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

	@Override
	public void updateStorage(PBaseVerifyprojectStorage storage) throws Throwable {
		Connection con = null;
		PreparedStatement pStmt = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("update p_base_verifyproject_storage set StorageCode='");
		sb.append(storage.getStorageCode()).append("',StorageName='");
		sb.append(storage.getStorageName()).append("'");
		//if(storage.getPBaseVerifyprojectCandiateId() != 0){
			sb.append(",CandiateID=").append(storage.getPBaseVerifyprojectCandiateId());
		//}
		//if(storage.getStorageAddress() != null && !storage.getStorageAddress().equals("")){
			sb.append(",StorageAddress='").append(storage.getStorageAddress().trim()).append("'");
		//}
		//if(storage.getStoragePhone() != null && !storage.getStoragePhone().equals("")){
			sb.append(",StoragePhone='").append(storage.getStoragePhone().trim()).append("'");
	//	}
		sb.append(",StorageTypeID=").append(storage.getPBaseVerifyprojectStoragetypeId());
		
		sb.append(" where StorageID=").append(storage.getStorageId());

		try {
			con = dataSource.getConnection();
			pStmt = con.prepareStatement(sb.toString());
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw new Exception(e.getMessage());
		} finally {
			try {
				DBUtil.setNullObject(con, pStmt, rs);
			} catch (Exception e) {
				throw new Exception(e.getMessage());
			}
		}
	}

}
